package com.ruibang.glass.quality.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruibang.glass.common.constants.CommonErrorCode;
import com.ruibang.glass.common.constants.RedisKey;
import com.ruibang.glass.frequencycontrol.util.RedisUtils;
import com.ruibang.glass.quality.constant.Constants;
import com.ruibang.glass.quality.constant.QualityErrorCode;
import com.ruibang.glass.quality.domain.req.ParticlePlotReq;
import com.ruibang.glass.quality.domain.resp.ColdPointLineChartResp;
import com.ruibang.glass.quality.domain.resp.ParticlePlot;
import com.ruibang.glass.quality.domain.resp.ParticlePlotData;
import com.ruibang.glass.quality.entity.ShelfLotItem;
import com.ruibang.glass.quality.mapper.ParticlePlotMapper;
import com.ruibang.glass.quality.service.ParticlePlotService;
import com.ruibang.glass.quality.util.PoiUtils;
import com.teaming.cloud.framework2.common.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @Author: songJinKang
 * @CreateTime: 2023-12-01  15:13
 * @Description: TODO
 * @Version: 1.0
 */
@Slf4j
@Service
public class ParticlePlotServiceImpl extends ServiceImpl<ParticlePlotMapper, ParticlePlot> implements ParticlePlotService {


    @Autowired
    @Qualifier("myRedisTemplate")
    private RedisTemplate<String, Object> redisTemplate;


    /**
     * @Description: 批次号与玻璃关联数据定时放入缓存
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-16 17:10
     */
    @Scheduled(cron = "0 */5 * * * ?")
    public void latestBindTimeTask() {
        List<ShelfLotItem> shelfLotItems = baseMapper.getGlassIdMappingBatchNumber();
        if (CollectionUtils.isNotEmpty(shelfLotItems)) {
            Map<String, List<ShelfLotItem>> shelfLotItemMap = shelfLotItems.stream().collect(Collectors.groupingBy(ShelfLotItem::getShelfLotId));
            shelfLotItemMap.forEach((k, v) -> {
                List<String> glassIds = v.stream().filter(e -> StringUtils.isNotBlank(e.getGlassId())).map(e -> {
                    try {
                        return e.getGlassId().substring(6);
                    } catch (Exception ex) {
                        log.error("非标准glassId ex：{}  glassId:：{}", ex.getMessage(), e.getGlassId());
                    }
                    return null;
                }).collect(Collectors.toList());
                redisTemplate.opsForHash().put(RedisKey.BATCH_NUMBER_MAPPING_GLASS_ID, k, JSONObject.toJSONString(glassIds));
            });
            RedisUtils.expire(RedisKey.BATCH_NUMBER_MAPPING_GLASS_ID, 30 * 24 * 60 * 60);
        }
    }


    /**
     * @Description: 根据批次号获取glassIds
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-16 17:24
     */
    private List<String> getGlassIdByBatchNumber(String batchNumber) {
        //先查缓存
        Object glassIdObj = redisTemplate.opsForHash().get(RedisKey.BATCH_NUMBER_MAPPING_GLASS_ID, batchNumber);
        if (glassIdObj != null && StringUtils.isNotBlank(glassIdObj.toString())) {
            return JSONObject.parseArray(glassIdObj.toString(), String.class);
        } else {
            //查询数据库  放入缓存
            List<String> glassIds = baseMapper.getGlassIdByBatchNumber(batchNumber);
            glassIds = glassIds.stream().map(e -> e.substring(6)).collect(Collectors.toList());
            redisTemplate.opsForHash().put(RedisKey.BATCH_NUMBER_MAPPING_GLASS_ID, batchNumber, JSONObject.toJSONString(glassIds));
            return glassIds;
        }
    }

    @Override
    public List<ParticlePlot> queryColdPoint(ParticlePlotReq particlePlotReq) {
        if (particlePlotReq == null || !isOnlyOneNotNull(particlePlotReq.getBatchNumber(), particlePlotReq.getGlassIds(), particlePlotReq.getStartTime(), particlePlotReq.getEndTime())) {
            throw new ServiceException(QualityErrorCode.INVALID_PARAM.getCode(), QualityErrorCode.INVALID_PARAM.getMsg());
        }
        //投入类型为空 默认为0
        if (StringUtils.isBlank(particlePlotReq.getIsDirectInvestment())) {
            particlePlotReq.setIsDirectInvestment(Constants.ZERO_STR);
        }

        List<ParticlePlot> data = new ArrayList<>();

        //根据批次号
        if (StringUtils.isNotBlank(particlePlotReq.getBatchNumber())) {
//            List<String> glassIds = baseMapper.getGlassIdByBatchNumber(particlePlotReq.getBatchNumber());
//            glassIds = glassIds.stream().map(e -> e.substring(6)).collect(Collectors.toList());
            List<String> glassIds = getGlassIdByBatchNumber(particlePlotReq.getBatchNumber());
            particlePlotReq.setGlassIds(glassIds);
            //查询缺陷数据
            if (CollectionUtils.isNotEmpty(particlePlotReq.getGlassIds())) {
                queryParticlePlotData(particlePlotReq, data);
            }
        } else {
            //根据时间、玻璃id查询
            queryParticlePlotData(particlePlotReq, data);
        }
        particlePlotReq.setGlassIds(null);

        return data.stream().sorted(Comparator.comparing(ParticlePlot::getDefectName)).collect(Collectors.toList());
    }


    @Override
    public ColdPointLineChartResp queryColdLineChart(ParticlePlotReq particlePlotReq) {
        if (particlePlotReq == null || !isOnlyOneNotNull(particlePlotReq.getBatchNumber(), particlePlotReq.getGlassIds(), particlePlotReq.getStartTime(), particlePlotReq.getEndTime())) {
            throw new ServiceException(QualityErrorCode.INVALID_PARAM.getCode(), QualityErrorCode.INVALID_PARAM.getMsg());
        }

        ColdPointLineChartResp coldPointLineChartResp = new ColdPointLineChartResp();
        //根据批次号
        if (StringUtils.isNotBlank(particlePlotReq.getBatchNumber())) {
//            List<String> glassIds = baseMapper.getGlassIdByBatchNumber(particlePlotReq.getBatchNumber());
//            glassIds = glassIds.stream().map(e -> e.substring(6)).collect(Collectors.toList());
            List<String> glassIds = getGlassIdByBatchNumber(particlePlotReq.getBatchNumber());
            particlePlotReq.setGlassIds(glassIds);
            //查询缺陷数据
            if (CollectionUtils.isNotEmpty(particlePlotReq.getGlassIds())) {
                queryColdLineChartData(particlePlotReq, coldPointLineChartResp);
            }
        } else {
            //根据时间、玻璃id查询
            queryColdLineChartData(particlePlotReq, coldPointLineChartResp);
        }
        particlePlotReq.setGlassIds(null);
        return coldPointLineChartResp;
    }

    @Override
    public void export(ParticlePlotReq particlePlotReq, HttpServletResponse response) {
        if (particlePlotReq == null || !isOnlyOneNotNull(particlePlotReq.getBatchNumber(), particlePlotReq.getGlassIds(), particlePlotReq.getStartTime(), particlePlotReq.getEndTime())) {
            throw new ServiceException(QualityErrorCode.INVALID_PARAM.getCode(), QualityErrorCode.INVALID_PARAM.getMsg());
        }

        //颗粒点位数据
        List<ParticlePlot> particlePlots = queryColdPoint(particlePlotReq);

        //折线堆积图数据
        ColdPointLineChartResp coldPointLineChartResp = queryColdLineChart(particlePlotReq);
        if (CollectionUtils.isEmpty(coldPointLineChartResp.getGlassId()) && CollectionUtils.isEmpty(particlePlots)) {
            throw new ServiceException(QualityErrorCode.NO_DATA_EXPORT.getCode(), QualityErrorCode.NO_DATA_EXPORT.getMsg());
        }

        //创建工作簿
        Workbook workbook = new SXSSFWorkbook(-1);
        Sheet abSheet = workbook.createSheet("A+B");
        Sheet zdSheet = workbook.createSheet("颗粒数量折线图");
//        Sheet totalSheet = workbook.createSheet("平均数值");

        List<CompletableFuture<Void>> taskList = new ArrayList<>();

        //处理颗粒数据表
        if (CollectionUtils.isNotEmpty(particlePlots)) {
            CompletableFuture<Void> task = CompletableFuture.runAsync(() -> {
                processPointData(abSheet, particlePlots);
            });
            taskList.add(task);
        }

        //处理折线堆积图数据
        if (coldPointLineChartResp != null) {
            CompletableFuture<Void> task = CompletableFuture.runAsync(() -> {
                processZDData(zdSheet, coldPointLineChartResp);
            });
            taskList.add(task);
        }

        //合计数据
//        totalData(totalSheet, coldPointLineChartResp, particlePlots);


        for (CompletableFuture<Void> completableFuture : taskList) {
            completableFuture.join();
        }

        //set样式
        PoiUtils.setGlobalStyle(workbook);
        //返回excel
        try {
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("PointData.xlsx", "utf-8"));
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            throw new ServiceException(CommonErrorCode.MONITOR_DATA_EXPORT_FAIL.getCode(), CommonErrorCode.MONITOR_DATA_EXPORT_FAIL.getMsg());
        }

    }


    public static boolean isOnlyOneNotNull(String batchNumber, List<String> glassIds, LocalDateTime startTime, LocalDateTime endTime) {
        return (StringUtils.isNotBlank(batchNumber) && CollectionUtils.isEmpty(glassIds) && (startTime == null && endTime == null)) ||
                (StringUtils.isBlank(batchNumber) && CollectionUtils.isNotEmpty(glassIds) && (startTime == null && endTime == null)) ||
                (StringUtils.isBlank(batchNumber) && CollectionUtils.isEmpty(glassIds) && (startTime != null && endTime != null));
    }

    /**
     * @Description: 处理颗粒点状图
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-07 18:13
     */
    private void queryParticlePlotData(ParticlePlotReq particlePlotReq, List<ParticlePlot> data) {
        List<ParticlePlotData> particlePlotData = baseMapper.queryDefectInfo(particlePlotReq);
        if (CollectionUtils.isNotEmpty(particlePlotData)) {
            Map<String, List<ParticlePlotData>> particlePlotDataMap = particlePlotData.stream().collect(Collectors.groupingBy(ParticlePlotData::getDefectName));
            particlePlotDataMap.forEach((k, v) -> {
                ParticlePlot particlePlot = new ParticlePlot();
                particlePlot.setParticlePlotData(v.stream().sorted(Comparator.comparing(ParticlePlotData::getStartTime)).collect(Collectors.toList()));
                particlePlot.setDefectName(k);
                data.add(particlePlot);
            });
        }
    }

    private void processPointData(Sheet abSheet, List<ParticlePlot> particlePlots) {
        if (CollectionUtils.isNotEmpty(particlePlots)) {
            AtomicInteger sumRowCount = new AtomicInteger(0);
            //设置表头
            Row headRow = abSheet.createRow(sumRowCount.get());
            for (int i = 0; i < Constants.TOTAL_AB_TABLE_HEADERS.length; i++) {
                Cell cell = headRow.createCell(i);
                cell.setCellValue(Constants.TOTAL_AB_TABLE_HEADERS[i]);
            }
            sumRowCount.addAndGet(Constants.ONE_INT);
            //set表格数据
            for (ParticlePlot particlePlot : particlePlots) {
                List<ParticlePlotData> particlePlotData = particlePlot.getParticlePlotData();
                particlePlotData = particlePlotData.stream().sorted(Comparator.comparing(ParticlePlotData::getGlassId)).collect(Collectors.toList());
                for (ParticlePlotData particlePlotDatum : particlePlotData) {
                    Row row = abSheet.createRow(sumRowCount.get());
                    for (int i = 0; i < 4; i++) {
                        Cell headRowCell = row.createCell(i);
                        if (i == 0) {
                            headRowCell.setCellValue(particlePlotDatum.getGlassId());
                            abSheet.setColumnWidth(i, 17 * 256);
                        }
                        if (i == 1) {
                            headRowCell.setCellValue(particlePlotDatum.getDefectName());
                            abSheet.setColumnWidth(i, 17 * 256);
                        }
                        if (i == 2) {
                            headRowCell.setCellValue(particlePlotDatum.getX());
                            abSheet.setColumnWidth(i, 17 * 256);
                        }
                        if (i == 3) {
                            headRowCell.setCellValue(particlePlotDatum.getY());
                            abSheet.setColumnWidth(i, 17 * 256);
                        }
                    }
                    sumRowCount.getAndAdd(Constants.ONE_INT);
                }
            }
//            AtomicInteger sRowCount = new AtomicInteger(0);
//            AtomicInteger mRowCount = new AtomicInteger(0);
//            AtomicInteger lRowCount = new AtomicInteger(0);
//            AtomicInteger olRowCount = new AtomicInteger(0);
//            for (ParticlePlot particlePlot : particlePlots) {
//                List<ParticlePlotData> particlePlotData = particlePlot.getParticlePlotData();
//                particlePlotData = particlePlotData.stream().sorted(Comparator.comparing(ParticlePlotData::getGlassId)).collect(Collectors.toList());
//                switch (particlePlot.getDefectName()) {
//                    case Constants.GLASS_POINT_DEFECT_SB:
//                    case Constants.GLASS_POINT_DEFECT_S: {
//                        //0-3 S、S_B
//                        setABDataTable(abSheet, sRowCount, particlePlotData, 0, 3);
//                        break;
//                    }
//                    case Constants.GLASS_POINT_DEFECT_M:
//                    case Constants.GLASS_POINT_DEFECT_MB: {
//                        //4-7 L、LB
//                        setABDataTable(abSheet, mRowCount, particlePlotData, 4, 7);
//                        break;
//                    }
//                    case Constants.GLASS_POINT_DEFECT_L:
//                    case Constants.GLASS_POINT_DEFECT_LB: {
//                        //8-11 M、MB
//                        setABDataTable(abSheet, lRowCount, particlePlotData, 8, 11);
//                        break;
//                    }
//                    case Constants.GLASS_POINT_DEFECT_OL:
//                    case Constants.GLASS_POINT_DEFECT_OLB: {
//                        //12-15 OL、OL_B
//                        setABDataTable(abSheet, olRowCount, particlePlotData, 12, 15);
//                        break;
//                    }
//                    default: {
//                        break;
//                    }
//                }
//            }
//            log.error("sRowCount:{}  mRowCount:{}  lRowCount:{}  olRowCount:{}",
//                    sRowCount, mRowCount, lRowCount, olRowCount);
        }
    }


    /**
     * @Description: 处理excel A+B面表格数据
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-13 14:20
     */
    private void setABDataTable(Sheet sheet, AtomicInteger currentCount, List<ParticlePlotData> particlePlotData, int leftPosition, int rightPosition) {
        //创建表头
        if (currentCount.get() == Constants.ZERO_INT) {
            Row headRow = sheet.getRow(Constants.ZERO_INT);
            if (headRow == null) {
                headRow = sheet.createRow(currentCount.get());
            }
            for (int i = leftPosition; i <= rightPosition; i++) {
                Cell headRowCell = headRow.createCell(i);
                if (i == 0 || i == 4 || i == 8 || i == 12) {
                    headRowCell.setCellValue("玻璃ID");
                    sheet.setColumnWidth(i, 17 * 256);
                }
                if (i == 1 || i == 5 || i == 9 || i == 13) {
                    headRowCell.setCellValue("缺陷名称");
                    sheet.setColumnWidth(i, 17 * 256);
                }
                if (i == 2 || i == 6 || i == 10 || i == 14) {
                    headRowCell.setCellValue("X");
                    sheet.setColumnWidth(i, 17 * 256);
                }
                if (i == 3 || i == 7 || i == 11 || i == 15) {
                    headRowCell.setCellValue("Y");
                    sheet.setColumnWidth(i, 17 * 256);
                }
            }
            currentCount.addAndGet(1);
        }
        //设置表格数据
        for (ParticlePlotData particlePlotDatum : particlePlotData) {
            Row row;
            int lastRowNum = sheet.getLastRowNum();
            if (currentCount.get() < lastRowNum) {
                row = sheet.getRow(currentCount.get());
            } else {
                row = sheet.createRow(lastRowNum + 1);
            }
            for (int i = leftPosition; i <= rightPosition; i++) {
                Cell headRowCell = row.createCell(i);
                if (i == 0 || i == 4 || i == 8 || i == 12) {
                    headRowCell.setCellValue(particlePlotDatum.getGlassId());
                }
                if (i == 1 || i == 5 || i == 9 || i == 13) {
                    headRowCell.setCellValue(particlePlotDatum.getDefectName());
                }
                if (i == 2 || i == 6 || i == 10 || i == 14) {
                    headRowCell.setCellValue(particlePlotDatum.getX());
                }
                if (i == 3 || i == 7 || i == 11 || i == 15) {
                    headRowCell.setCellValue(particlePlotDatum.getY());
                }
            }
            currentCount.addAndGet(1);
        }
    }

    /**
     * @Description: 合计sheet表
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-13 15:47
     */
//    private void totalData(Sheet totalSheet, ColdPointLineChartResp coldPointLineChartResp, List<ParticlePlot> particlePlots) {
//        //表1
//        if (coldPointLineChartResp != null) {
//            Row row = totalSheet.createRow(0);
//            //设置表头
//            for (int i = 0; i < Constants.TOTAL_POINT_NUM_HEADERS.length; i++) {
//                Cell cell = row.createCell(i);
//                cell.setCellValue(Constants.TOTAL_POINT_NUM_HEADERS[i]);
//            }
//            //A面数据
//            Row aRow = totalSheet.createRow(1);
//            aRow.createCell(0).setCellValue("A面");
//            aRow.createCell(1).setCellValue(coldPointLineChartResp.getS().stream().mapToInt(Integer::intValue).sum());
//            aRow.createCell(2).setCellValue(coldPointLineChartResp.getM().stream().mapToInt(Integer::intValue).sum());
//            aRow.createCell(3).setCellValue(coldPointLineChartResp.getL().stream().mapToInt(Integer::intValue).sum());
//            aRow.createCell(4).setCellValue(coldPointLineChartResp.getOl().stream().mapToInt(Integer::intValue).sum());
//            int aSum = coldPointLineChartResp.getASum().stream().mapToInt(Integer::intValue).sum();
//            aRow.createCell(5).setCellValue(aSum);
//            aRow.createCell(6).setCellValue((double) aSum / 4);
//            //B面数据
//            Row bRow = totalSheet.createRow(2);
//            bRow.createCell(0).setCellValue("B面");
//            bRow.createCell(1).setCellValue(coldPointLineChartResp.getSb().stream().mapToInt(Integer::intValue).sum());
//            bRow.createCell(2).setCellValue(coldPointLineChartResp.getMb().stream().mapToInt(Integer::intValue).sum());
//            bRow.createCell(3).setCellValue(coldPointLineChartResp.getLb().stream().mapToInt(Integer::intValue).sum());
//            bRow.createCell(4).setCellValue(coldPointLineChartResp.getOlb().stream().mapToInt(Integer::intValue).sum());
//            int bSum = coldPointLineChartResp.getBSum().stream().mapToInt(Integer::intValue).sum();
//            bRow.createCell(5).setCellValue(bSum);
//            bRow.createCell(6).setCellValue((double) bSum / 4);
//
//        }
//
//        //表2
//        if (CollectionUtils.isNotEmpty(particlePlots)) {
//            Row row = totalSheet.createRow(0);
//            //设置表头
//            for (int i = 0; i < Constants.TOTAL_PROPORTION_HEADERS.length; i++) {
//                Cell cell = row.createCell(i);
//                cell.setCellValue(Constants.TOTAL_PROPORTION_HEADERS[i]);
//            }
//            //颗粒数≤60
//            Row lowRow = totalSheet.createRow(1);
//            lowRow.createCell(0).setCellValue("颗粒数≤60");
//            lowRow.createCell(1).setCellValue("");
//            lowRow.createCell(2).setCellValue("");
//
//            //60＜颗粒数≤80
//            Row centerRow = totalSheet.createRow(2);
//            centerRow.createCell(0).setCellValue("60＜颗粒数≤80");
//            centerRow.createCell(1).setCellValue("");
//            centerRow.createCell(2).setCellValue("");
//
//            //颗粒数＞80
//            Row tallRow = totalSheet.createRow(3);
//            tallRow.createCell(0).setCellValue("颗粒数＞80");
//            tallRow.createCell(1).setCellValue("");
//            tallRow.createCell(2).setCellValue("");
//        }
//    }

    /**
     * @Description: 处理折线图缺陷数量统计数据
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-13 14:20
     */
    private void processZDData(Sheet zdSheet, ColdPointLineChartResp coldPointLineChartResp) {
        List<String> glassIds = coldPointLineChartResp.getGlassId();
        List<Integer> s = coldPointLineChartResp.getS();
        List<Integer> m = coldPointLineChartResp.getM();
        List<Integer> l = coldPointLineChartResp.getL();
        List<Integer> ol = coldPointLineChartResp.getOl();
        List<Integer> sb = coldPointLineChartResp.getSb();
        List<Integer> mb = coldPointLineChartResp.getMb();
        List<Integer> lb = coldPointLineChartResp.getLb();
        List<Integer> olb = coldPointLineChartResp.getOlb();
        List<Integer> abSum = coldPointLineChartResp.getAbSum();
        List<Integer> aSum = coldPointLineChartResp.getASum();
        List<Integer> bSum = coldPointLineChartResp.getBSum();
        if (CollectionUtils.isNotEmpty(glassIds)) {
            //创建表头
            Row headerRow = zdSheet.createRow(0);
            for (int i = 0; i < Constants.ZD_HEADERS.length; i++) {
                Cell cell = headerRow.createCell(i);
                zdSheet.setColumnWidth(i, 17 * 256);
                cell.setCellValue(Constants.ZD_HEADERS[i]);
            }
            //填充数据
            for (int i = 0; i < glassIds.size(); i++) {
                Row row = zdSheet.createRow(i + 1);
                row.createCell(0).setCellValue(glassIds.get(i));
                row.createCell(1).setCellValue(s.get(i) + sb.get(i));
                row.createCell(2).setCellValue(m.get(i) + mb.get(i));
                row.createCell(3).setCellValue(l.get(i) + lb.get(i));
                row.createCell(4).setCellValue(ol.get(i) + olb.get(i));
                row.createCell(5).setCellValue(abSum.get(i));
                row.createCell(6).setCellValue(80);
                row.createCell(7).setCellValue(s.get(i));
                row.createCell(8).setCellValue(m.get(i));
                row.createCell(9).setCellValue(l.get(i));
                row.createCell(10).setCellValue(ol.get(i));
                row.createCell(11).setCellValue(aSum.get(i));
                row.createCell(12).setCellValue(sb.get(i));
                row.createCell(13).setCellValue(mb.get(i));
                row.createCell(14).setCellValue(lb.get(i));
                row.createCell(15).setCellValue(olb.get(i));
                row.createCell(16).setCellValue(bSum.get(i));
            }
        }

        //总数
        int sumRowCount = zdSheet.getLastRowNum() + 1;
        Row sumRow = zdSheet.createRow(sumRowCount);
        sumRow.createCell(0).setCellValue("总数");
        sumRow.createCell(1).setCellValue(sumColumnData(zdSheet, 1));
        sumRow.createCell(2).setCellValue(sumColumnData(zdSheet, 2));
        sumRow.createCell(3).setCellValue(sumColumnData(zdSheet, 3));
        sumRow.createCell(4).setCellValue(sumColumnData(zdSheet, 4));
        //合并单元格
//            zdSheet.addMergedRegion(new CellRangeAddress(sumRowCount, 5, sumRowCount, 6));
        sumRow.createCell(7).setCellValue(sumColumnData(zdSheet, 7));
        sumRow.createCell(8).setCellValue(sumColumnData(zdSheet, 8));
        sumRow.createCell(9).setCellValue(sumColumnData(zdSheet, 9));
        sumRow.createCell(10).setCellValue(sumColumnData(zdSheet, 10));
        sumRow.createCell(11).setCellValue("");
        sumRow.createCell(12).setCellValue(sumColumnData(zdSheet, 12));
        sumRow.createCell(13).setCellValue(sumColumnData(zdSheet, 13));
        sumRow.createCell(14).setCellValue(sumColumnData(zdSheet, 14));
        sumRow.createCell(15).setCellValue(sumColumnData(zdSheet, 15));
        sumRow.createCell(16).setCellValue("");

        //平均值
        int avgRowCount = zdSheet.getLastRowNum() + 1;
        Row avgRow = zdSheet.createRow(avgRowCount);
        avgRow.createCell(0).setCellValue("平均值");
        avgRow.createCell(1).setCellValue(calculateColumnAverage(zdSheet, 1));
        avgRow.createCell(2).setCellValue(calculateColumnAverage(zdSheet, 2));
        avgRow.createCell(3).setCellValue(calculateColumnAverage(zdSheet, 3));
        avgRow.createCell(4).setCellValue(calculateColumnAverage(zdSheet, 4));
        //合并单元格
//            zdSheet.addMergedRegion(new CellRangeAddress(avgRowCount, 5, avgRowCount, 6));
        avgRow.createCell(7).setCellValue(calculateColumnAverage(zdSheet, 7));
        avgRow.createCell(8).setCellValue(calculateColumnAverage(zdSheet, 8));
        avgRow.createCell(9).setCellValue(calculateColumnAverage(zdSheet, 9));
        avgRow.createCell(10).setCellValue(calculateColumnAverage(zdSheet, 10));
        avgRow.createCell(11).setCellValue("");
        avgRow.createCell(12).setCellValue(calculateColumnAverage(zdSheet, 12));
        avgRow.createCell(13).setCellValue(calculateColumnAverage(zdSheet, 13));
        avgRow.createCell(14).setCellValue(calculateColumnAverage(zdSheet, 14));
        avgRow.createCell(15).setCellValue(calculateColumnAverage(zdSheet, 15));
        avgRow.createCell(16).setCellValue("");
    }

    public double calculateColumnAverage(Sheet sheet, int columnIndex) {
        int rowCount = sheet.getLastRowNum() + 1; // 获取总行数
        double sum = 0;
        int count = 0; // 记录有效单元格的数量
        for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(columnIndex);
                if (cell != null && cell.getCellTypeEnum() == CellType.NUMERIC) {
                    sum += cell.getNumericCellValue();
                    count++;
                }
            }
        }
        if (count > 0) {
            return sum / count;
        } else {
            return 0; // 或者根据需要返回其他默认值
        }
    }

    public int sumColumnData(Sheet sheet, int columnIndex) {
        int rowCount = sheet.getLastRowNum() + 1; // 获取总行数
        int sum = 0;
        for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(columnIndex);
                if (cell != null && cell.getCellTypeEnum() == CellType.NUMERIC) {
                    sum += cell.getNumericCellValue();
                }
            }
        }
        return sum;
    }

    /**
     * @Description: 处理颗粒折线图数据
     * @version v1.0
     * @author songJinKang
     * @date 2023-12-07 18:13
     */
    private void queryColdLineChartData(ParticlePlotReq particlePlotReq, ColdPointLineChartResp coldPointLineChartResp) {
        List<ParticlePlotData> particlePlotData = baseMapper.queryDefectInfo(particlePlotReq);
        if (CollectionUtils.isNotEmpty(particlePlotData)) {

            List<String> glassIds = new ArrayList<>();
            List<Integer> s = new ArrayList<>();
            List<Integer> m = new ArrayList<>();
            List<Integer> l = new ArrayList<>();
            List<Integer> ol = new ArrayList<>();
            List<Integer> sb = new ArrayList<>();
            List<Integer> mb = new ArrayList<>();
            List<Integer> lb = new ArrayList<>();
            List<Integer> olb = new ArrayList<>();
            List<Integer> abSum = new ArrayList<>();
            List<Integer> aSum = new ArrayList<>();
            List<Integer> bSum = new ArrayList<>();

            Set<String> sGlassNum = new HashSet<>();
            Set<String> mGlassNum = new HashSet<>();
            Set<String> lGlassNum = new HashSet<>();
            Set<String> olGlassNum = new HashSet<>();
            Set<String> sbGlassNum = new HashSet<>();
            Set<String> mbGlassNum = new HashSet<>();
            Set<String> lbGlassNum = new HashSet<>();
            Set<String> olbGlassNum = new HashSet<>();

            Map<String, List<ParticlePlotData>> particlePlotDataMap = particlePlotData.stream().collect(Collectors.groupingBy(ParticlePlotData::getGlassId));

            for (String key : particlePlotDataMap.keySet()) {
                glassIds.add(key);
                List<ParticlePlotData> defectData = particlePlotDataMap.get(key);
                Map<String, List<ParticlePlotData>> defectDataMap = defectData.stream().collect(Collectors.groupingBy(ParticlePlotData::getDefectName));
                Set<String> defectNames = defectDataMap.keySet();
                List<String> glassPointDefectList = Constants.GLASS_POINT_DEFECT_LIST;
                List<String> distinctNames = Stream.concat(defectNames.stream(), glassPointDefectList.stream()).collect(Collectors.groupingBy(Function.identity(), Collectors.counting())).entrySet().stream().filter(e -> e.getValue() == 1).map(Map.Entry::getKey).collect(Collectors.toList());
                if (CollectionUtils.isNotEmpty(distinctNames)) {
                    for (String distinctName : distinctNames) {
                        defectDataMap.put(distinctName, Collections.emptyList());
                    }
                }


                AtomicInteger aCount = new AtomicInteger(0);
                AtomicInteger bCount = new AtomicInteger(0);
                AtomicInteger abCount = new AtomicInteger(0);
                defectDataMap.forEach((defectName, totalData) -> {
                    int size = totalData.size();
                    //统计缺陷数量与玻璃片数
                    abCount.addAndGet(size);
                    switch (defectName) {
                        case Constants.GLASS_POINT_DEFECT_S: {
                            //统计缺陷数
                            s.add(size);
                            aCount.addAndGet(size);
                            //统计玻璃片数
                            sGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_M: {
                            //统计缺陷数
                            m.add(totalData.size());
                            aCount.addAndGet(size);
                            //统计玻璃片数
                            mGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_L: {
                            //统计缺陷数
                            l.add(totalData.size());
                            aCount.addAndGet(size);
                            //统计玻璃片数
                            lGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_OL: {
                            //统计缺陷数
                            ol.add(totalData.size());
                            aCount.addAndGet(size);
                            //统计玻璃片数
                            olGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_SB: {
                            //统计缺陷数
                            sb.add(totalData.size());
                            bCount.addAndGet(size);
                            //统计玻璃片数
                            sbGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_MB: {
                            //统计缺陷数
                            mb.add(totalData.size());
                            bCount.addAndGet(size);
                            //统计玻璃片数
                            mbGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_LB: {
                            //统计缺陷数
                            lb.add(totalData.size());
                            bCount.addAndGet(size);
                            //统计玻璃片数
                            lbGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        case Constants.GLASS_POINT_DEFECT_OLB: {
                            //统计缺陷数
                            olb.add(totalData.size());
                            bCount.addAndGet(size);
                            //统计玻璃片数
                            olbGlassNum.addAll(totalData.stream().map(ParticlePlotData::getGlassId).collect(Collectors.toList()));
                            break;
                        }
                        default: {
                            log.error("颗粒点图没有类型--> {}", defectName);
                        }
                    }
                });
                abSum.add(abCount.get());
                aSum.add(aCount.get());
                bSum.add(bCount.get());
            }

            //玻璃缺陷数据统计
            coldPointLineChartResp.setGlassId(glassIds);
            coldPointLineChartResp.setS(s);
            coldPointLineChartResp.setM(m);
            coldPointLineChartResp.setL(l);
            coldPointLineChartResp.setOl(ol);
            coldPointLineChartResp.setSb(sb);
            coldPointLineChartResp.setMb(mb);
            coldPointLineChartResp.setLb(lb);
            coldPointLineChartResp.setOlb(olb);
            coldPointLineChartResp.setAbSum(abSum);
            coldPointLineChartResp.setASum(aSum);
            coldPointLineChartResp.setBSum(bSum);

            coldPointLineChartResp.setSDefectNum(s.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setMDefectNum(m.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setLDefectNum(l.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setOlDefectNum(ol.stream().reduce(Integer::sum).orElse(0));

            coldPointLineChartResp.setSbDefectNum(sb.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setMbDefectNum(mb.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setLbDefectNum(lb.stream().reduce(Integer::sum).orElse(0));
            coldPointLineChartResp.setOlbDefectNum(olb.stream().reduce(Integer::sum).orElse(0));

            coldPointLineChartResp.setSDefectTotalNum(coldPointLineChartResp.getSDefectNum() + coldPointLineChartResp.getSbDefectNum());
            coldPointLineChartResp.setMDefectTotalNum(coldPointLineChartResp.getMDefectNum() + coldPointLineChartResp.getMbDefectNum());
            coldPointLineChartResp.setLDefectTotalNum(coldPointLineChartResp.getLDefectNum() + coldPointLineChartResp.getLbDefectNum());
            coldPointLineChartResp.setOlDefectTotalNum(coldPointLineChartResp.getOlDefectNum() + coldPointLineChartResp.getOlbDefectNum());

            //玻璃片数统计
            coldPointLineChartResp.setSGlassNum(coldPointLineChartResp.getSDefectNum() / sGlassNum.size());
            coldPointLineChartResp.setMGlassNum(coldPointLineChartResp.getMDefectNum() / mGlassNum.size());
            coldPointLineChartResp.setLGlassNum(coldPointLineChartResp.getLDefectNum() / lGlassNum.size());
            coldPointLineChartResp.setOlGlassNum(coldPointLineChartResp.getOlDefectNum() / olGlassNum.size());

            coldPointLineChartResp.setSbGlassNum(coldPointLineChartResp.getSbDefectNum() / sbGlassNum.size());
            coldPointLineChartResp.setMbGlassNum(coldPointLineChartResp.getMbDefectNum() / mbGlassNum.size());
            coldPointLineChartResp.setLbGlassNum(coldPointLineChartResp.getLbDefectNum() / lbGlassNum.size());
            coldPointLineChartResp.setOlbGlassNum(coldPointLineChartResp.getOlbDefectNum() / olbGlassNum.size());

            coldPointLineChartResp.setSGlassTotalNum(coldPointLineChartResp.getSGlassNum() + coldPointLineChartResp.getSbGlassNum());
            coldPointLineChartResp.setMGlassTotalNum(coldPointLineChartResp.getMGlassNum() + coldPointLineChartResp.getMbGlassNum());
            coldPointLineChartResp.setLGlassTotalNum(coldPointLineChartResp.getLGlassNum() + coldPointLineChartResp.getLbGlassNum());
            coldPointLineChartResp.setOlGlassTotalNum(coldPointLineChartResp.getOlGlassNum() + coldPointLineChartResp.getOlbGlassNum());


            //玻璃总数
            coldPointLineChartResp.setGlassTotalNum(glassIds.size());

        }
    }
}
